﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using DB.SchemaSync.Library.Models;
using DB.SchemaSync.SqlServer.Models;

namespace DB.SchemaSync.SqlServer
{
	public partial class SqlServerDbProvider
	{
		private async Task<IEnumerable<Table>> GetTablesAsync(IDbConnection connection)
		{
			var tables = await connection.QueryAsync<Table>(
@"WITH [clusteredIndexes] AS (
	SELECT [name], [object_id] FROM [sys].[indexes] WHERE [type_desc]='CLUSTERED'
), [identityColumns] AS (
	SELECT [object_id], [name] FROM [sys].[columns] WHERE [is_identity]=1
) SELECT
	[t].[name] AS [Name],
	SCHEMA_NAME([t].[schema_id]) AS [Schema],
	[t].[object_id] AS [ObjectId],
	[c].[name] AS [ClusteredIndex],
	[i].[name] AS [IdentityColumn],
	(SELECT SUM(row_count) FROM [sys].[dm_db_partition_stats] WHERE [object_id]=[t].[object_id] AND [index_id] IN (0, 1)) AS [RowCount]
FROM
	[sys].[tables] [t]
	LEFT JOIN [clusteredIndexes] [c] ON [t].[object_id]=[c].[object_id]
	LEFT JOIN [identityColumns] [i] ON [t].[object_id]=[i].[object_id]
WHERE
	([t].[name] NOT LIKE 'AspNet%' OR [t].[name] LIKE 'AspNetUsers') AND
	[t].[name] NOT IN ('__MigrationHistory', '__EFMigrationsHistory')");

			var columns = await connection.QueryAsync<Column>(
@"SELECT
	[col].[object_id] AS [ObjectId],
	[col].[name] AS [Name],
	TYPE_NAME([col].[system_type_id]) AS [DataType],
	[col].[is_nullable] AS [IsNullable],
	[def].[definition]  AS [Default],
	[col].[collation_name] AS [Collation],
	CASE
		WHEN TYPE_NAME([col].[system_type_id]) LIKE 'nvar%' AND [col].[max_length]>0 THEN ([col].[max_length]/2)
		WHEN TYPE_NAME([col].[system_type_id]) LIKE 'nvar%' AND [col].[max_length]=0 THEN -1
		ELSE [col].[max_length]
	END AS [MaxLength],
	[col].[precision] AS [Precision],
	[col].[scale] AS [Scale],
	[col].[column_id] AS [InternalId],
	[calc].[definition] AS [Expression],
    [def].[name] AS [DefName],
    [rem].[value] AS [Description]
FROM
	[sys].[columns] [col]
	INNER JOIN [sys].[tables] [t] ON [col].[object_id]=[t].[object_id]
	LEFT JOIN [sys].[default_constraints] [def] ON [col].[default_object_id]=[def].[object_id]
	LEFT JOIN [sys].[computed_columns] [calc] ON [col].[object_id]=[calc].[object_id] AND [col].[column_id]=[calc].[column_id]
	LEFT JOIN [sys].[extended_properties] [rem] ON [rem].[major_id] = [col].[object_id] AND [rem].[minor_id] = [col].[column_id]
WHERE
	[t].[type_desc]='USER_TABLE'");

			var indexes = await connection.QueryAsync<Index>(
@"SELECT
	[x].[object_id] AS [ObjectId],
	[x].[name] AS [Name],
	CONVERT(bit, CASE
		WHEN [x].[type_desc]='CLUSTERED' THEN 1
		ELSE 0
	END) AS [IsClustered],
	CASE
		WHEN [x].[is_primary_key]=1 THEN 1
		WHEN [x].[is_unique]=1 AND [x].[is_unique_constraint]=0 THEN 2
		WHEN [x].[is_unique_constraint]=1 THEN 3
		WHEN [x].[is_unique]=0 THEN 4
	END AS [Type],
	[x].[index_id] AS [InternalId]
FROM
	[sys].[indexes] [x]
	INNER JOIN [sys].[tables] [t] ON [x].[object_id]=[t].[object_id]
WHERE
	[t].[type_desc]='USER_TABLE' AND
	[x].[type]<>0");

			var indexCols = await connection.QueryAsync<IndexColumnResult>(
@"SELECT
    [xcol].[object_id],
    [xcol].[index_id],
    [col].[name],
    [xcol].[key_ordinal],
    [xcol].[is_descending_key]
FROM
    [sys].[index_columns] [xcol]
    INNER JOIN [sys].[indexes] [x] ON [xcol].[object_id]=[x].[object_id] AND [xcol].[index_id]=[x].[index_id]
    INNER JOIN [sys].[columns] [col] ON [xcol].[object_id]=[col].[object_id] AND [xcol].[column_id]=[col].[column_id]
    INNER JOIN [sys].[tables] [t] ON [x].[object_id]=[t].[object_id]
WHERE
    [t].[type_desc]='USER_TABLE'");


            var triggers = await connection.QueryAsync<Trigger>(
@"SELECT tr.[object_id] ObjectId,tr.[parent_id] Parent_Id, tr.[name] [Name]
,tr.[is_instead_of_trigger] [Is_Instead_Of],
tr.[is_disabled] [Is_Disabled], tr.[type] ,tr.[type_desc]
FROM sys.triggers  tr
INNER JOIN [sys].[tables] [t] ON [tr].[parent_id]=[t].[object_id]
WHERE
[t].[type_desc]='USER_TABLE'");

            //todo... 触发器事件待完成
            var triggerEvents = await connection.QueryAsync<Trigger_Event>(
@"SELECT tre.[object_id] ObjectId , tre.type ,tre.type_desc ,tre.is_trigger_event  
FROM sys.trigger_events tre 
INNER JOIN sys.triggers  tr ON tre.object_id = tr.object_id
INNER JOIN [sys].[tables] [t] ON [tr].[parent_id]=[t].[object_id]
WHERE
[t].[type_desc]='USER_TABLE'");



            var columnLookup = columns.ToLookup(row => row.ObjectId);
			var indexLookup = indexes.ToLookup(row => row.ObjectId);
			var indexColLookup = indexCols.ToLookup(row => 
                new IndexKey() { object_id = row.object_id, index_id = row.index_id });

            var trgLookup = triggers.ToLookup(row => row.Parent_Id);
            var treLookup = triggerEvents.ToLookup(row => row.ObjectId);

			foreach (var x in indexes)
			{
				var indexKey = new IndexKey() { object_id = x.ObjectId, index_id = x.InternalId };
				x.Columns = indexColLookup[indexKey].Select(row => new IndexColumn()
				{
					Name = row.name,
					Position = row.key_ordinal,
					SortDirection = (row.is_descending_key) ? SortDirection.Descending : SortDirection.Ascending
				});
			}

            //添加表中的集合对象
			foreach (var t in tables)
			{
                //添加表列
				t.Columns = columnLookup[t.ObjectId].ToArray();
                foreach (var col in t.Columns)
                {
                    col.Table = t;
                }

                //添加表索引
				t.Indexes = indexLookup[t.ObjectId].ToArray();
                foreach (var x in t.Indexes)
                {
                    x.Table = t;
                }

                //添加触发器 
                t.Triggers = trgLookup[t.ObjectId].ToArray();
                foreach (var trg in t.Triggers)
                {
                    trg.Table = t;
                    var sqls = await connection.QueryAsync<string>($"EXEC sp_helptext  '{trg.Name}'");
                    trg.CreateSql = sqls.Aggregate(" ", (x, y) => x + " \n " + y);
                    trg.Events = treLookup[trg.ObjectId].ToArray();
                }
			}

			return tables;
		}
	}
}